package com.shop.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.shop.dao.Basedao;
import com.shop.entity.SHOP_PRODUCT;

public class SHOP_PRODUCTDao {
	 public static int insert(SHOP_PRODUCT p) {
		  
   	  String sql = "insert into shop_product values(?, ?, ?, ?, ?, ?, ?)"; 
   	  Object[] params= {
   			  p.getPRODUCT_NAME(),
   			  p.getPRODUCT_DESCRIPTION(),
   			  p.getPRODUCT_PRICE(),
   			  p.getPRODUCT_STOCK(),
   			  p.getPRODUCT_FID(),
   			  p.getPRODUCT_CID(),
   			  p.getPRODUCT_FILENAME()
  	  
   	  };
		return Basedao.exectuIUD(sql, params);
     }
	
	
	 public static ArrayList<SHOP_PRODUCT> selectAll() {
		 ArrayList<SHOP_PRODUCT> list=new ArrayList<SHOP_PRODUCT>();
	   	 ResultSet rs = null;//声明结果集
	   	 //获取连接对象
	   	 Connection conn = Basedao.getconn();
	   	 PreparedStatement ps = null;
	   	   
	   	 try {
	   		 String sql = "select * from shop_product ";
	   		 ps = conn.prepareStatement(sql);
	   		 rs=ps.executeQuery();
	   		 while(rs.next()) {
	   			 SHOP_PRODUCT p = new SHOP_PRODUCT(
	   					 rs.getInt("PRODUCT_ID"),
	   					 rs.getString("PRODUCT_NAME"),
	   					 rs.getString("PRODUCT_DESCRIPTION"),
						 rs.getInt("PRODUCT_PRICE"),
						 rs.getInt("PRODUCT_STOCK"),
						 rs.getInt("PRODUCT_FID"),
						 rs.getInt("PRODUCT_CID"),
						 rs.getString("PRODUCT_FILENAME")
						 );
	   			 list.add(p);
	   			 }
	   		 } catch (SQLException e) {
	   			 // TODO Auto-generated catch block
				 e.printStackTrace();
				 }finally {
					 Basedao.closeall(rs, ps, conn);
					 }
	   	 return list;
	   	 }
	 public static ArrayList<SHOP_PRODUCT> selectByAllFid(int fid) {
		 ArrayList<SHOP_PRODUCT> list=new ArrayList<SHOP_PRODUCT>();
	   	 ResultSet rs = null;//声明结果集
	   	 //获取连接对象
	   	 Connection conn = Basedao.getconn();
	   	 PreparedStatement ps = null;
	   	 
	   	 try {
	   		 String sql = "select * from shop_product where PRODUCT_FID=?";
	   		 ps = conn.prepareStatement(sql);
	   		 
	   		 ps.setInt(1, fid);
	   		 rs=ps.executeQuery();
	   		 while(rs.next()) {
	   			 SHOP_PRODUCT p = new SHOP_PRODUCT(
							rs.getInt("PRODUCT_ID"),
							rs.getString("PRODUCT_NAME"),
							rs.getString("PRODUCT_DESCRIPTION"),
							rs.getInt("PRODUCT_PRICE"),
							rs.getInt("PRODUCT_STOCK"),
							rs.getInt("PRODUCT_FID"),
							rs.getInt("PRODUCT_CID"),
							rs.getString("PRODUCT_FILENAME")
							
							
							
							);
					list.add(p);
					}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				Basedao.closeall(rs, ps, conn);
			}
	   	  return list;
	 }
	 
	 public static ArrayList<SHOP_PRODUCT> selectByAllCid(int cid) {
	   	  ArrayList<SHOP_PRODUCT> list=new ArrayList<SHOP_PRODUCT>();
	   	  ResultSet rs = null;//声明结果集
	   	  //获取连接对象
	   	  Connection conn = Basedao.getconn();
	   	   PreparedStatement ps = null;
	   	   
	   	   try { 
	   	String sql = "select * from shop_product where PRODUCT_CID=?";
	   	ps = conn.prepareStatement(sql);
		
	   	ps.setInt(1, cid);
	   	
	   	
		rs=ps.executeQuery();
	  
				while(rs.next()) {
					SHOP_PRODUCT p = new SHOP_PRODUCT(
							rs.getInt("PRODUCT_ID"),
							rs.getString("PRODUCT_NAME"),
							rs.getString("PRODUCT_DESCRIPTION"),
							rs.getInt("PRODUCT_PRICE"),
							rs.getInt("PRODUCT_STOCK"),
							rs.getInt("PRODUCT_FID"),
							rs.getInt("PRODUCT_CID"),
							rs.getString("PRODUCT_FILENAME")
							
							
							
							);
					list.add(p);
					}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				Basedao.closeall(rs, ps, conn);
			}
	   	  return list;
	     }
	 
	 public static SHOP_PRODUCT selectById(int id) {
	   	 SHOP_PRODUCT p = null;
	   	 ResultSet rs = null;//声明结果集
	   	  //获取连接对象
	   	  Connection conn = Basedao.getconn();
	   	   PreparedStatement ps = null;
	   	   
	   	   try { 
	   	String sql = "select * from shop_product where PRODUCT_ID=?";
	   	ps = conn.prepareStatement(sql);
		ps.setInt(1, id);
		rs=ps.executeQuery();
	  
				while(rs.next()) {
					 p = new SHOP_PRODUCT(
							rs.getInt("PRODUCT_ID"),
							rs.getString("PRODUCT_NAME"),
							rs.getString("PRODUCT_DESCRIPTION"),
							rs.getInt("PRODUCT_PRICE"),
							rs.getInt("PRODUCT_STOCK"),
							rs.getInt("PRODUCT_FID"),
							rs.getInt("PRODUCT_CID"),
							rs.getString("PRODUCT_FILENAME")
							
							
							
							);
					
					}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				Basedao.closeall(rs, ps, conn);
			}
	   	  return p;
	     }

	 
	 public static ArrayList<SHOP_PRODUCT> selectAllById(ArrayList<Integer> ids) {
	   	  ArrayList<SHOP_PRODUCT> lastlylist=new ArrayList<SHOP_PRODUCT>();
	   	  SHOP_PRODUCT p = null;
	   	  
	   	  ResultSet rs = null;//声明结果集
	   	  //获取连接对象
	   	  Connection conn = Basedao.getconn();
	   	  PreparedStatement ps = null;
	   	   
	   	  try {
	   		  for(int i=0; i<ids.size(); i++) {
	   			  String sql = "select * from shop_product where PRODUCT_ID=?";
	   		   	  ps = conn.prepareStatement(sql);
	   			  
	   		   	  ps.setInt(1, ids.get(i));
	   		   	
	   			  rs=ps.executeQuery();
	   			  while(rs.next()) {
	   				  p = new SHOP_PRODUCT(
	   						  rs.getInt("PRODUCT_ID"),
	   						  rs.getString("PRODUCT_NAME"),
	   						  rs.getString("PRODUCT_DESCRIPTION"),
	   						  rs.getInt("PRODUCT_PRICE"),
	   						  rs.getInt("PRODUCT_STOCK"),
	   						  rs.getInt("PRODUCT_FID"),
	   						  rs.getInt("PRODUCT_CID"),
	   						  rs.getString("PRODUCT_FILENAME")
	   						  );
	   				  lastlylist.add(p);
	   				  }
	   			  }
	   		  } catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				Basedao.closeall(rs, ps, conn);
			}
	   	  return lastlylist;
	   	  }
	 /**
	  * 删除图书
	  * @param id
	  * @return
	  */
	 public static int del(int id) {
   	  String sql = "delete from shop_product where PRODUCT_ID=?";
   	 Object[] params = {id};
   	  return Basedao.exectuIUD(sql, params);
     } 
	  
	 
	 
	 /**
	  * 
	  * @param id
	  * @return
	  */
	 public static SHOP_PRODUCT selectByID(int id) {
    	 SHOP_PRODUCT p = null;
    	 
    	 ResultSet rs = null;//声明结果集
    	  //获取连接对象
    	 Connection conn = Basedao.getconn();
    	  
    	 PreparedStatement ps = null;
    	 
    	 try {
    		 String sql = "select * from shop_product where PRODUCT_ID=?";
    		 ps = conn.prepareStatement(sql);
    		 ps.setInt(1, id);
    		 rs=ps.executeQuery();
    		 while(rs.next()) {
    			 p = new SHOP_PRODUCT(
    					 rs.getInt("PRODUCT_ID"),
						 rs.getString("PRODUCT_NAME"),
						 rs.getString("PRODUCT_DESCRIPTION"),
						 rs.getInt("PRODUCT_PRICE"),
						 rs.getInt("PRODUCT_STOCK"),
						 rs.getInt("PRODUCT_FID"),
						 rs.getInt("PRODUCT_CID"),
						 rs.getString("PRODUCT_FILENAME")
						 );
				}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			Basedao.closeall(rs, ps, conn);
		}
    	  return p;
      }


	public static int update(SHOP_PRODUCT product) {
		// TODO Auto-generated method stub
		int count = 0;
		
		return count;
	}
	 
}